using Exhort.Entity.Basic;
using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using Microsoft.EntityFrameworkCore;

namespace Exhort.DbSet.Basic
{
    public class BasicFunctionDbSet : OrderDbSet<BasicFunction>
    {
        public List<BasicFunction> GetByMenuId(string menuId)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("SELECT F.ID");
            sql.AppendLine(",F.InsertTime");
            sql.AppendLine(",F.UpdateTime");
            sql.AppendLine(",F.Sequence");
            sql.AppendLine(",F.Title");
            sql.AppendLine(",F.Icon");
            sql.AppendLine(",F.Type");
            sql.AppendLine(",F.Value");
            sql.AppendLine(",F.Attrs");
            sql.AppendLine(",F.Remark");
            sql.AppendLine("FROM Basic_Function AS F");
            sql.AppendLine("INNER JOIN Basic_MenuFunction AS MF ON MF.FunctionID = F.ID");
            sql.AppendLine("WHERE MF.MenuID = @MenuID");
            sql.AppendLine("ORDER BY MF.Sequence ASC");

            return dbContext.Dapper.Query<BasicFunction>(sql.ToString(), new { MenuID = menuId }).ToList();
        }

        public List<BasicFunction> EffectiveAll(string menuId)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("SELECT F.ID");
            sql.AppendLine(",F.InsertTime");
            sql.AppendLine(",F.UpdateTime");
            sql.AppendLine(",F.Sequence");
            sql.AppendLine(",F.Title");
            sql.AppendLine(",F.Icon");
            sql.AppendLine(",F.Type");
            sql.AppendLine(",F.Value");
            sql.AppendLine(",F.Attrs");
            sql.AppendLine(",F.Remark");
            sql.AppendLine("FROM Basic_Function AS F");
            sql.AppendLine("INNER JOIN Basic_MenuFunction AS MF ON MF.FunctionID = F.ID");
            sql.AppendLine("WHERE MF.MenuID = @MenuID");
            sql.AppendLine("AND F.Type = 'button'");
            sql.AppendLine("ORDER BY MF.Sequence ASC");

            return dbContext.Dapper.Query<BasicFunction>(sql.ToString(), new { MenuID = menuId }).ToList();
        }

        public List<BasicFunction> EffectiveByUserId(string menuId, string userId)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("SELECT DISTINCT F.ID");
            sql.AppendLine(",F.InsertTime");
            sql.AppendLine(",F.UpdateTime");
            sql.AppendLine(",F.Sequence");
            sql.AppendLine(",F.Title");
            sql.AppendLine(",F.Icon");
            sql.AppendLine(",F.Type");
            sql.AppendLine(",F.Value");
            sql.AppendLine(",F.Attrs");
            sql.AppendLine(",F.Remark");
            sql.AppendLine(",MF.Sequence AS MFSequence");
            sql.AppendLine("FROM Basic_Function AS F");
            sql.AppendLine("INNER JOIN Basic_MenuFunction AS MF ON MF.FunctionID = F.ID");
            sql.AppendLine("INNER JOIN Basic_RoleMenuFunction AS RMF ON RMF.MenuFunctionID = MF.ID");
            sql.AppendLine("INNER JOIN Basic_UserRole AS UR ON UR.RoleID = RMF.RoleID");
            sql.AppendLine("INNER JOIN Basic_Role AS R ON R.ID = UR.RoleID");
            sql.AppendLine("WHERE MF.MenuID = @MenuID");
            sql.AppendLine("AND F.Type = 'button'");
            sql.AppendLine("AND UR.UserID = @UserID");
            sql.AppendLine("AND R.IsEnabled = 1");
            sql.AppendLine("ORDER BY MF.Sequence ASC");

            return dbContext.Dapper.Query<BasicFunction>(sql.ToString(), new { MenuID = menuId, UserID = userId }).ToList();
        }
    }
}